package com.hexu.event.dao;

import com.hexu.event.entity.Attractions;
import com.hexu.event.entity.TicketHistory;
import com.hexu.event.entity.User;
import com.hexu.event.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TicketHistoryDao {


    //添加购票历史
    public boolean addTicketHistory(TicketHistory ticketHistory){
        String sql = "insert into ticket_history(user_phone_number,buy_time,attractions_name,ticket_fare)"
                + " values (?,?,?,?);";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConn();
            ps = conn.prepareStatement(sql);
            //View层set的数据
            ps.setString(1, ticketHistory.getUserPhoneNumber());
            ps.setString(2, ticketHistory.getBuyTime());
            ps.setString(3,ticketHistory.getAttractionsName());
            ps.setInt(4,ticketHistory.getTicketFare());
            ps.executeUpdate();

            //添加成功
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JdbcUtil.close(conn, ps, null);
        }

        //添加失败
        return false;
    }

    //返回一个用户的购票历史
    public List< TicketHistory> getAUserTicketHistory(String phoneNumber){
        List< TicketHistory> list = new ArrayList<>();

        String sql = "select * from ticket_history where user_phone_number=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            ps = conn.prepareStatement(sql);
            ps.setString(1,phoneNumber);
            rs = ps.executeQuery();
            while (rs.next()){
                TicketHistory ticketHistory = new TicketHistory();
                ticketHistory.setId(rs.getInt("id"));
                ticketHistory.setUserPhoneNumber(rs.getString("user_phone_number"));
                ticketHistory.setBuyTime(rs.getString("buy_time"));
                ticketHistory.setAttractionsName(rs.getString("attractions_name"));
                ticketHistory.setTicketFare(rs.getInt("ticket_fare"));

                list.add(ticketHistory);
            }
            return list;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn, ps, rs);
        }
        return null;
    }

    //返回一个用户购买历史的门票数量
    public int aUserTicketNumber(String phoneNumber){
        String sql = "select user_phone_number from ticket_history where user_phone_number=? ";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            //使游标可滚动
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setString(1,phoneNumber);
            rs = ps.executeQuery();

            // 将游标移动到最后一行上
            rs.last();
            //获取景点数
            int ticketCount = rs.getRow();
            //把光标移至第一行前
            rs.beforeFirst();

            return ticketCount;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn, ps, rs);
        }
        return 0;
    }

    //返回所有用户的购票历史
    public List< TicketHistory> getAllUserTicketHistory(){
        List< TicketHistory> list = new ArrayList<>();

        String sql = "select * from ticket_history ";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                TicketHistory ticketHistory = new TicketHistory();
                ticketHistory.setId(rs.getInt("id"));
                ticketHistory.setUserPhoneNumber(rs.getString("user_phone_number"));
                ticketHistory.setBuyTime(rs.getString("buy_time"));
                ticketHistory.setAttractionsName(rs.getString("attractions_name"));
                ticketHistory.setTicketFare(rs.getInt("ticket_fare"));

                list.add(ticketHistory);
            }
            return list;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn, ps, rs);
        }
        return null;
    }

    //返回所有用户购买历史的门票数量
    public int allUserTicketNumber(){
        String sql = "select user_phone_number from ticket_history ";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            //使游标可滚动
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            // 将游标移动到最后一行上
            rs.last();
            //获取景点数
            int ticketCount = rs.getRow();
            //把光标移至第一行前
            rs.beforeFirst();

            return ticketCount;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn, ps, rs);
        }
        return 0;
    }
}
